package cn.xiu.drugstore.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.xiu.drugstore.dao.StaffDao;
import cn.xiu.drugstore.pojo.Staff;
import cn.xiu.drugstore.util.BaseDao;

/**
 * 员工功能实现类
 * 
 * @author 陈亚鹏
 * 
 */
public class StaffDaoImpl extends BaseDao implements StaffDao {
	/**
	 * 查看所有店员信息
	 */
	@Override
	public List<Staff> queryAllStaff() throws Exception {
		List<Staff> list = new ArrayList<Staff>();
		String sql = "SELECT staff_pwd, staff_id,staff_name,staff_age,staff_identity,staff_phone,staff_workday,staff_address,staff_type FROM staff ORDER BY staff_id DESC ";
		ResultSet rs = super.executeQuery(sql, null);
		while (rs.next()) {
			Staff staff = new Staff();
			staff.setStaffId(rs.getInt("staff_id"));
			staff.setStaffName(rs.getString("staff_name"));
			staff.setStaffAge(rs.getInt("staff_age"));
			staff.setStaffIdentity(rs.getString("staff_identity"));
			staff.setStaffPhone(rs.getString("staff_phone"));
			staff.setStaffWorkDay(rs.getDouble("staff_workday"));
			staff.setStaffAddress(rs.getString("staff_address"));
			staff.setStaffType(rs.getInt("staff_type"));
			staff.setStaffPass(rs.getString("staff_pwd"));
			list.add(staff);
		}
		super.closeResource(rs);
		return list;
	}

	/**
	 * 查看指定ID店员
	 */
	@Override
	public Staff queryStaffById(int staffid) throws Exception {
		Staff staff = null;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT staff_pwd,staff_id,staff_name,staff_age");
		sql.append(",staff_identity,staff_phone,staff_workday");
		sql.append(",staff_address,staff_type");
		sql.append(" FROM staff WHERE staff_id=?");
		Object[] params = { staffid };
		ResultSet rs = super.executeQuery(sql.toString(), params);
		if (rs != null && rs.next()) {
			staff = new Staff();
			staff.setStaffPass(rs.getString("staff_pwd"));
			staff.setStaffId(rs.getInt("staff_id"));
			staff.setStaffName(rs.getString("staff_name"));
			staff.setStaffAge(rs.getInt("staff_age"));
			staff.setStaffIdentity(rs.getString("staff_identity"));
			staff.setStaffPhone(rs.getString("staff_phone"));
			staff.setStaffWorkDay(rs.getDouble("staff_workday"));
			staff.setStaffAddress(rs.getString("staff_address"));
			staff.setStaffType(rs.getInt("staff_type"));
		}
		super.closeResource(rs);
		return staff;
	}

	/**
	 * 增加店员
	 */
	@Override
	public int addStaff(Staff staff) throws Exception {
		String sql = "INSERT INTO staff (staff_pwd,staff_id,staff_name,staff_age,staff_identity,staff_phone,staff_workday,staff_address,staff_type) VALUES(?,?,?,?,?,?,?,?,?)";
		Object[] params = { staff.getStaffPass(), staff.getStaffId(),
				staff.getStaffName(), staff.getStaffAge(),
				staff.getStaffIdentity(), staff.getStaffPhone(),
				staff.getStaffWorkDay(), staff.getStaffAddress(),
				staff.getStaffType() };
		int result = super.executeUpdate(sql, params);
		super.closeResource(null);
		return result;
	}

	/**
	 * 删除店员
	 */
	@Override
	public int deleteStaff(int id) throws Exception {
		String sql = "DELETE FROM  staff WHERE staff_id=?";
		Object[] params = { id };
		return super.executeUpdate(sql, params);
	}

	@Override
	public Staff queryStaffByName(String staffName) throws Exception {
		Staff staff = null;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT staff_pwd,staff_id,staff_name,staff_age");
		sql.append(",staff_identity,staff_phone,staff_workday");
		sql.append(",staff_address,staff_type");
		sql.append(" FROM staff WHERE staff_name=?");
		Object[] params = { staffName };
		ResultSet rs = super.executeQuery(sql.toString(), params);
		if (rs != null && rs.next()) {
			staff = new Staff();
			staff.setStaffPass(rs.getString("staff_pwd"));
			staff.setStaffId(rs.getInt("staff_id"));
			staff.setStaffName(rs.getString("staff_name"));
			staff.setStaffAge(rs.getInt("staff_age"));
			staff.setStaffIdentity(rs.getString("staff_identity"));
			staff.setStaffPhone(rs.getString("staff_phone"));
			staff.setStaffWorkDay(rs.getDouble("staff_workday"));
			staff.setStaffAddress(rs.getString("staff_address"));
			staff.setStaffType(rs.getInt("staff_type"));
		}
		super.closeResource(rs);
		return staff;
	}

	@Override
	public int queryTotalRecord(String queryName, int roleId) throws Exception {
		List<Object> paramsList = new ArrayList<Object>();
		int row = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(0) FROM staff WHERE 1=1");
		if (queryName != null && !"".equals(queryName)) {
			sql.append(" AND staff_name LIKE ?");
			paramsList.add("%" + queryName + "%");
		}
		if (roleId >= 0) {
			sql.append(" AND staff_type=?");
			paramsList.add(roleId);
		}
		// 把List直接转化为Object[]数组
		Object[] params = paramsList.toArray();
		ResultSet rs = super.executeQuery(sql.toString(), params);
		if (rs != null && rs.next()) {
			row = rs.getInt(1);
		}
		super.closeResource(rs);
		return row;
	}

	@Override
	public List<Staff> queryStaffByOffset(int offset, int pageSize,
			String queryName, int roleId) throws Exception {
		List<Staff> list = new ArrayList<Staff>();
		StringBuffer sql = new StringBuffer();
		List paramsList = new ArrayList();
		sql.append("SELECT staff_id,staff_name,staff_age,staff_identity,staff_phone,staff_workday,staff_address,staff_type FROM staff WHERE 1=1");
		if (queryName != null && !"".equals(queryName)) {
			sql.append(" AND staff_name LIKE ?");
			paramsList.add("%" + queryName + "%");
		}
		if (roleId >= 0) {
			sql.append(" AND staff_type=?");
			paramsList.add(roleId);
		}
		sql.append(" ORDER BY staff_id DESC LIMIT ?,? ");
		paramsList.add(offset);
		paramsList.add(pageSize);
		Object[] params = paramsList.toArray();
		ResultSet rs = super.executeQuery(sql.toString(), params);
		while (rs != null && rs.next()) {
			Staff staff = new Staff();
			staff.setStaffId(rs.getInt("staff_id"));
			staff.setStaffName(rs.getString("staff_name"));
			staff.setStaffAge(rs.getInt("staff_age"));
			staff.setStaffIdentity(rs.getString("staff_identity"));
			staff.setStaffPhone(rs.getString("staff_phone"));
			staff.setStaffWorkDay(rs.getInt("staff_workday"));
			staff.setStaffAddress(rs.getString("staff_address"));
			staff.setStaffType(rs.getInt("staff_type"));
			list.add(staff);
		}
		super.closeResource(rs);
		return list;
	}
}
